﻿/******************************************************/
// DALNews データアクセスクラス  
/******************************************************/

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System;

namespace MAA.Data
{
    /// <summary>
    /// ニュースを参照のデータベースへのプロセスデータアクセス 
    /// </summary>
    public class DALNews
    {
        private string conStr = ConfigurationManager.AppSettings["connectionString"];

        /// <summary>
        /// Get information of 6 newest market news
        /// </summary>
        /// <returns></returns>
        public DataTable getNewsUpdate(string lang)
        {
            DataTable newsDataTable = new DataTable();
            SqlDataAdapter newsAdapter = new SqlDataAdapter();
            newsAdapter.SelectCommand = new SqlCommand();

            if (lang.Equals("vi"))
            {
                newsAdapter.SelectCommand.CommandText = @"
                    SELECT TOP 6	
			                    News.ID,
			                    News.Title,
			                    News.PostDate,
			                    News.[Image],
			                    News.Content,
			                    CONVERT(varchar, News.PostDate, 105) AS UpdateTime
                    FROM		News
                    ORDER BY	News.PostDate";
            }
            else
            {
                newsAdapter.SelectCommand.CommandText = @"
                    SELECT TOP 6	
			                    News.ID,
			                    News.TitleJP AS Title,
			                    News.PostDate,
			                    News.[Image],
			                    News.ContentJP AS Content,
			                    CONVERT(varchar, News.PostDate, 105) AS UpdateTime
                    FROM		News
                    ORDER BY	News.PostDate";
            }

            newsAdapter.SelectCommand.Connection = new SqlConnection(conStr);
            newsAdapter.Fill(newsDataTable);
            newsAdapter.SelectCommand.Connection.Close();

            return newsDataTable;
        }

        /// <summary>
        /// Get information of all market news
        /// </summary>
        /// <returns></returns>
        public DataTable getAllNews(string lang, int pageIndex, int pageSize, out long rowNum)
        {
            DataTable tblNews = new DataTable();
            rowNum = 0;

            SqlConnection conn = new SqlConnection(conStr);
            SqlCommand cmd;
            cmd = new SqlCommand("GetNews", conn);

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@Lang", SqlDbType.VarChar).Value = lang;
            cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
            cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;

            cmd.Parameters.Add("@RecordCount", SqlDbType.BigInt);
            cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(tblNews);
            rowNum = Convert.ToInt64(cmd.Parameters["@RecordCount"].Value);
            conn.Close();

            return tblNews;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="newsID"></param>
        /// <returns></returns>
        public DataTable getNewsRandom(string lang, int newsID)
        {
            DataTable newsDataTable = new DataTable();
            SqlDataAdapter newsAdapter = new SqlDataAdapter();
            newsAdapter.SelectCommand = new SqlCommand();

            if (lang.Equals("vi"))
            {
                newsAdapter.SelectCommand.CommandText = @"
                        SELECT TOP 4	
			                        News.ID,
			                        News.Title,
			                        News.PostDate,
			                        News.[Image],
			                        News.Content,
			                        CONVERT(varchar, News.PostDate, 105) AS UpdateTime
                        FROM		News
                        WHERE       News.ID != @newsID
                        ORDER BY	NEWID()";
            }
            else
            {
                newsAdapter.SelectCommand.CommandText = @"
                        SELECT TOP 4	
			                        News.ID,
			                        News.TitleJP AS Title,
			                        News.PostDate,
			                        News.[Image],
			                        News.ContentJP AS Content,
			                        CONVERT(varchar, News.PostDate, 105) AS UpdateTime
                        FROM		News
                        WHERE       News.ID != @newsID
                        ORDER BY	NEWID()";
            }

            newsAdapter.SelectCommand.Parameters.Add("@newsID", SqlDbType.Int).Value = newsID;
            newsAdapter.SelectCommand.Connection = new SqlConnection(conStr);
            newsAdapter.Fill(newsDataTable);
            newsAdapter.SelectCommand.Connection.Close();

            return newsDataTable;
        }

        /// <summary>
        /// Get detail information of a market news
        /// </summary>
        /// <param name="newsID"></param>
        /// <returns></returns>
        public DataTable getNewsInfo(string lang, int newsID)
        {
            DataTable newsDataTable = new DataTable();
            SqlDataAdapter newsAdapter = new SqlDataAdapter();
            newsAdapter.SelectCommand = new SqlCommand();

            if (lang.Equals("vi"))
            {
                newsAdapter.SelectCommand.CommandText = @"
                    SELECT		News.Title,
			                    News.PostDate,
			                    News.[Image],
			                    News.Content,
			                    [Admin].Name,
                                CONVERT(varchar, News.PostDate, 105) AS UpdateTime
                    FROM		News,[Admin]
                    WHERE		[Admin].ID = News.AdminID
	                    AND		News.ID = @newsID";
            }
            else
            {
                newsAdapter.SelectCommand.CommandText = @"
                    SELECT		News.TitleJP AS Title,
			                    News.PostDate,
			                    News.[Image],
			                    News.ContentJP AS Content,
			                    [Admin].Name,
                                CONVERT(varchar, News.PostDate, 105) AS UpdateTime
                    FROM		News,[Admin]
                    WHERE		[Admin].ID = News.AdminID
	                    AND		News.ID = @newsID";
            }

            newsAdapter.SelectCommand.Parameters.Add("@newsID", SqlDbType.Int).Value = newsID;

            newsAdapter.SelectCommand.Connection = new SqlConnection(conStr);
            newsAdapter.Fill(newsDataTable);
            newsAdapter.SelectCommand.Connection.Close();

            return newsDataTable;
        }

        /// <summary>
        /// Get name of all provinces
        /// </summary>
        /// <returns></returns>
        public DataTable getProvince(string lang)
        {
            DataTable tblProvince = new DataTable();
            string sql;
            // String query to get password of user by id
            if (lang.Equals("vi"))
            {
                sql = @"SELECT   ID,
                                Name
                        FROM    dbo.[Province]";
            }
            else
            {
                sql = @"SELECT   ID,
                                NameJP AS Name
                        FROM    dbo.[Province]";
            }

            SqlConnection conn = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand(sql, conn);

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(tblProvince);
            conn.Close();

            return tblProvince;
        }
    }
}